<?php
FLEA::loadClass('FLEA_Db_TableDataGateway');

class	Model_Invention	extends		FLEA_Db_TableDataGateway
{
	var		$tableName		=	'invention';
	var		$primaryKey		=	'InventionID';

	function 	findAllInventions($userID,$role)
	{
		$sql	=	"	
				
			select distinct invention.*,client.Business as ClientName,firm.Name as FirmName,
			inventorUser.Email as InventorEmail,inventorUser.FirstName as InventorFirstName,inventorUser.LastName as InventorLastName,
			practitionerUser.Email as PractitionerEmail,practitionerUser.FirstName as PractititonerFirstName,
			practitionerUser.LastName as PractitionerLastName,auction.Valuation as Value,
			action1.Date as LastActionDate , 
			(
					select sum(action.Cost) from action 
					where action.InventionID = invention.InventionID and action.Type=2 
				)  as FillingCost,
				
				(
					select sum(action.Cost) from action 
					where action.InventionID = invention.InventionID and (action.Type=3 or action.Type=5)
				)  as ExaminationCost,
				
				(
					select sum(action.Cost) from action 
					where action.InventionID = invention.InventionID and (action.Type=2 or action.Type=3 or action.Type=5)
				)  as TotalCost
			from 
			
			invention
							
			left join client on invention.ClientID=client.ClientID 
							
			left join firm on invention.FirmID=firm.FirmID
							
			left join inventor on invention.InventorID=inventor.InventorID
							
			left join practitioner on invention.PractitionerID=practitioner.PractitionerID
				
			left join user as inventorUser on inventorUser.UserID=inventor.UserID
				
			left join user as practitionerUser on practitionerUser.UserID=practitioner.UserID
				
			left join auction  on invention.InventionID=auction.InventionID
				
			left join action as action1 on action1.ActionID in ( select max(action.ActionID) from action where action.InventionID=invention.InventionID)
				
			";
		switch($role)
		{
			case 1:
				$sql	.=	" where invention.ClientID=".$userID."";
				break;
			case 2:
				$sql	.=	" where invention.InventorID=".$userID;
				break;
			case 3:
				$sql	.=	"left join bid  on invention.InventionID=bid.ID and bid.Type=0 and bid.FirmID=".$userID."
						 where invention.FirmID=".$userID;
				break;
			case 4:
				$sql	.=	" where invention.PractitionerID=".$userID;
				break;
		}
		$sql	.=	"  group by invention.InventionID order by invention.ActionDueDate desc";
		
		return $this->findBySql($sql);
	}
	function	findPublishedInventions()
	{
			
	}
	/**
	 * find all inventions of the client
	 *
	 * @param int $clientID
	 * @return array $inventions
	 */
	function	findBidInventions($firmID)
	{
		$firmID	=	$firmID==''?0:$firmID;
		$sql	=
			
			"select invention.*,client.Business as ClientName,firm.Name as FirmName,
				inventorUser.Email as InventorEmail,inventorUser.FirstName as InventorFirstName,inventorUser.LastName as InventorLastName,
				practitionerUser.Email as PractitionerEmail,practitionerUser.FirstName as PractititonerFirstName,
				practitionerUser.LastName as PractitionerLastName,
				bid.Budget from 
			
				invention
							
				left join client on invention.ClientID=client.ClientID 
							
				left join firm on invention.FirmID=firm.FirmID
							
				left join inventor on invention.InventorID=inventor.InventorID
							
				left join practitioner on invention.PractitionerID=practitioner.PractitionerID
				
				left join user as inventorUser on inventorUser.UserID=inventor.UserID
				
				left join user as practitionerUser on practitionerUser.UserID=practitioner.UserID
				
				left join bid on bid.ID=invention.InventionID and Bid.Type=0 and bid.FirmID=".$firmID.
			" where invention.BBid=1 and invention.FirmID=0";
			
		$inventions	=	$this->findBySql($sql);
		return $inventions;	
	}

	/**
	 * find invention through inventionID
	 *
	 * @param int $inventionID
	 * @return array
	 */
	function	findInvention($inventionID)
	{
		$sql	=
			
			"	select invention.*,client.Business as ClientName,client.Description as ClientDescription,firm.Name as FirmName,
				inventorUser.Email as InventorEmail,inventorUser.FirstName as InventorFirstName,inventorUser.LastName as InventorLastName,
				practitionerUser.Email as PractitionerEmail,practitionerUser.FirstName as PractitionerFirstName,
				practitionerUser.LastName as PractitionerLastName,
				(
					select sum(action.Cost) from action 
					where action.InventionID = invention.InventionID and action.Type=2 
				)  as FillingCost,
				
				(
					select sum(action.Cost) from action 
					where action.InventionID = invention.InventionID and (action.Type=3 or action.Type=5)
				)  as ExaminationCost,
				
				(
					select sum(action.Cost) from action 
					where action.InventionID = invention.InventionID and (action.Type=2 or action.Type=3 or action.Type=5)
				)  as TotalCost
				
				 from 
			
				invention
							
				left join client on invention.ClientID=client.ClientID 
							
				left join firm on invention.FirmID=firm.FirmID
							
				left join inventor on invention.InventorID=inventor.InventorID
							
				left join practitioner on invention.PractitionerID=practitioner.PractitionerID
				
				left join user as inventorUser on inventorUser.UserID=inventor.UserID
				
				left join user as practitionerUser on practitionerUser.UserID=practitioner.UserID
				
				where invention.InventionID=".$inventionID."  group by invention.InventionID";
			
		$inventions	=	$this->findBySql($sql);
		return $inventions[0];
	}
	/**
	 * create a new invention for the inventor
	 *
	 * @param array $invention
	 * @return int
	 */
	function	addInvention($invention)
	{
		return $this->create($invention);
	}
	/**
	 * assign a invention the firm by client
	 *
	 * @param int $inventionID
	 * @param int $firmID
	 */
	function	assignToFirm($inventionID,$firmID)
	{
		$invention['InventionID']	=	$inventionID;
		$invention['FirmID']		=	$firmID;
		return $this->save($invention);
	}
	/**
	 * assign the invention to the practitioner by firm
	 *
	 * @param int $inventionID
	 * @param int $practitionerID
	 * @return int
	 */
	function	assignToPractitioner($inventionID,$practitionerID)
	{
		$invention['InventionID']	=	$inventionID;
		$invention['PractitionerID']=	$practitionerID;
		return $this->save($invention);
	}

	/*function	findTrackInventions($clientID,$year)
		{
		$sql	=
			
		"	select distinct invention.*,client.Business as ClientName,firm.Name as FirmName,
		inventorUser.Email as InventorEmail,inventorUser.FirstName as InventorFirstName,inventorUser.LastName as InventorLastName,
		practitionerUser.Email as PractitionerEmail,practitionerUser.FirstName as PractititonerFirstName,
		practitionerUser.LastName as PractitionerLastName,auction.Valuation as Value,
		action1.Date as LastActionDate , action2.DueDate as ActionDueDate , month(invention.Created) as Month
		from
			
		invention
			
		left join client on invention.ClientID=client.ClientID
			
		left join firm on invention.FirmID=firm.FirmID
			
		left join inventor on invention.InventorID=inventor.InventorID
			
		left join practitioner on invention.PractitionerID=practitioner.PractitionerID

		left join user as inventorUser on inventorUser.UserID=inventor.UserID

		left join user as practitionerUser on practitionerUser.UserID=practitioner.UserID

		left join auction  on invention.InventionID=auction.InventionID

		left join action as action1 on action1.ActionID in ( select max(action.ActionID) from action where action.InventionID=invention.InventionID)

		left join action as action2 on action2.DueDate = (select max(action.DueDate) from action where action.InventionID=invention.InventionID)
		where invention.ClientID=".$clientID." and year(invention.Created) = ".$year;
			
		$inventions	=	$this->findBySql($sql);
		return $inventions;
		}*/

	function	findTrackInventions($clientID,$year)
	{
		$sql	=
			"
				select invention.InventionID ,invention.Status,month(invention.Created) as Month ,
				
				(select sum(action.Cost) from action 
				where action.InventionID=invention.InventionID and action.Type=2) 
				as SumFillingCost,
				
				(select sum(action.Cost) from action 
				where action.InventionID=invention.InventionID and action.Type=5) 
				as SumResponseCost
			
				from invention,action where invention.ClientID=".$clientID."
				and invention.InventionID = action.InventionID and year(invention.Created)=".$year."
				and (invention.Status=9 or invention.Status=10)
				group by invention.InventionID
			";
		
		$inventions	=	$this->findBySql($sql);
		return $inventions;
	}
	
	function	findTrackInventionsByFirm($clientID)
	{
		$sql	=
			"
				select invention.InventionID,invention.Title as InventionName,invention.FirmID,firm.Name as FirmName , invention.Status as InventionType , 
				action.Type as ActionType , action.Cost as ActionCost
			
				from invention,action,firm where invention.ClientID=".$clientID."
				and invention.FirmID != 0
				and invention.InventionID = action.InventionID and (action.Type=2 or action.Type=5 or action.Type=3)
				and (invention.Status=9 or invention.Status=10)
				and firm.FirmID=invention.FirmID
				order by invention.FirmID asc, invention.InventionID asc
			";
		
		$inventions	=	$this->findBySql($sql);
		return $inventions;
	}

	function	searchInventions($keyWords)
	{
		$sql	=
			
			"	select distinct invention.*,client.Business as ClientName,firm.Name as FirmName,
				inventorUser.Email as InventorEmail,inventorUser.FirstName as InventorFirstName,inventorUser.LastName as InventorLastName,
				practitionerUser.Email as PractitionerEmail,practitionerUser.FirstName as PractititonerFirstName,
				practitionerUser.LastName as PractitionerLastName,auction.Valuation as Value,
				action1.Date as LastActionDate , action2.DueDate as ActionDueDate
				 from 
			
				invention
							
				left join client on invention.ClientID=client.ClientID 
							
				left join firm on invention.FirmID=firm.FirmID
							
				left join inventor on invention.InventorID=inventor.InventorID
							
				left join practitioner on invention.PractitionerID=practitioner.PractitionerID
				
				left join user as inventorUser on inventorUser.UserID=inventor.UserID
				
				left join user as practitionerUser on practitionerUser.UserID=practitioner.UserID
				
				left join auction  on invention.InventionID=auction.InventionID
				
				left join action as action1 on action1.ActionID in ( select max(action.ActionID) from action where action.InventionID=invention.InventionID)
				
				left join action as action2 on action2.DueDate = (select max(action.DueDate) from action where action.InventionID=invention.InventionID) 
				where 
				
				 (invention.Title like '%".$keyWords."%' or invention.KeyWords like '%".$keyWords."%')";
		
		$inventions	=	$this->findBySql($sql);
		return $inventions;	
	}
	
	function	findTracks($clientID,$year,$nextYear)
	{
		$sql	=	"
			select ClientID,invention.Invented , 0 as Type,year(Invented) as Year,month(Invented) as Month from invention where invention.Status=0
			and ClientID=".$clientID." and ( year(Invented)=".$year." || year(Invented)=".$nextYear." )
			union all
			
			select ClientID,invention.Invented , 1 as Type,year(Invented) as Year,month(Invented) as Month from invention where invention.Status=3
			and ClientID=".$clientID." and (year(Invented)=".$year." || year(Invented)=".$nextYear." )
			union all
			
			select ClientID,invention.Invented , 2 as Type,year(Invented) as Year,month(Invented) as Month from invention where invention.Status=3
			and ClientID=".$clientID." and (year(Invented)=".$year." || year(Invented)=".$nextYear." )
			union all
			
			select ClientID,invention.Invented , 3 as Type,year(Invented) as Year,month(Invented) as Month from invention where invention.Status=2
			and ClientID=".$clientID." and (year(Invented)=".$year." || year(Invented)=".$nextYear." )
			union all
			
			select ClientID,invention.Invented , 4 as Type,year(Invented) as Year,month(Invented) as Month from invention where invention.Status=9
			and ClientID=".$clientID." and (year(Invented)=".$year." || year(Invented)=".$nextYear." )
			union all
			
			select ClientID,invention.Invented , 5 as Type,year(Invented) as Year,month(Invented) as Month from invention where invention.Status=10
			and ClientID=".$clientID." and (year(Invented)=".$year." || year(Invented)=".$nextYear." )
			union all
			
			select ClientID,trademark.Invented , 6 as Type,year(Invented) as Year,month(Invented) as Month from trademark
			where ClientID=".$clientID." and (year(Invented)=".$year." || year(Invented)=".$nextYear." )
			union all
			
			select ClientID,tradesecret.Invented , 6 as Type,year(Invented) as Year,month(Invented) as Month from tradesecret
			where ClientID=".$clientID." and (year(Invented)=".$year." || year(Invented)=".$nextYear." )
			union all
			
			select ClientID,copyright.Invented , 6 as Type,year(Invented) as Year,month(Invented) as Month from copyright
			where ClientID=".$clientID." and (year(Invented)=".$year." || year(Invented)=".$nextYear." )
			 order by Invented
		";
		
		return $this->findBySql($sql);
	}
}
?>